package com.matt.repository.nursery;

import com.matt.domain.nursery.NurserySchedule;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;

import java.util.Date;
import java.util.List;

/**
 * *******************************
 * Created by lch.
 * form com.matt
 * on 2018/11/26
 * *******************************
 */
public interface NurseryScheduleRepository extends JpaRepository<NurserySchedule, String>, JpaSpecificationExecutor<NurserySchedule> {

    @Query(nativeQuery = true, value = "select *  from nursery_schedule where classesId=?1 and date(lessonDate)=?2 " +
            " and teacherId is null and childrenId is null limit 1 ")
    NurserySchedule findTop1ByClassesIdAndLessonDate(String classesId, String lessonDate);

    @Query(nativeQuery = true, value = "select *  from nursery_schedule  where teacherId=?1 and childrenId=?2 and date(lessonDate)=?3 " +
            " and classesId is null limit 1")
    NurserySchedule findTop1ByTeacherIdAndChildrenIdAndLessonDate(String teacherId, String childrenId, String lessonDate);

    @Query(nativeQuery = true, value = "select *  from nursery_schedule  where childrenId=?1 and date(lessonDate)=?2 " +
            " and classesId is null")
    List<NurserySchedule> findByChildrenIdAndLessonDate(String childrenId, String lessonDate);

    List<NurserySchedule> findByAreaIdAndClassesIdAndLessonDate(String areaId, String classesId,Date lessonDate);

    List<NurserySchedule> findByAreaIdAndClassesIdAndLessonDateAndTeacherIdIsNullAndClassesIdIsNull(String areaId, String classesId,Date lessonDate);

    List<NurserySchedule> findByAreaIdAndChildrenIdAndTeacherIdAndLessonDateAndClassesIdIsNull(String areaId, String childrenId, String teacherId, Date lessonDate);
    @Query(nativeQuery = true, value = "select *  from nursery_schedule  where areaId=?1 and teacherId=?2 " +
            " and childrenId is not null group by childrenId")
    List<NurserySchedule> findAllByAreaIdAndTeacherId(String areaId, String teacherId);
    @Query(nativeQuery = true, value = "select *  from nursery_schedule  where areaId=?1 and teacherId is not null" +
            " and childrenId is not null group by childrenId")
    List<NurserySchedule> findAllByAreaIdAndTeacherIdIsNull(String areaId);

}
